import sqlite3
from typing import List, Optional, Dict, Any
from schemas import JudgeCreate, ContestantCreate, ScoreCreate, ScoreResponse, ContestantResponse

def create_judge(db: sqlite3.Connection, judge: JudgeCreate) -> Dict[str, Any]:
    """创建评委"""
    try:
        cursor = db.cursor()
        cursor.execute(
            "INSERT INTO judges (name) VALUES (?)",
            (judge.name,)
        )
        db.commit()
        
        # 获取刚插入的记录
        cursor.execute("SELECT * FROM judges WHERE id = ?", (cursor.lastrowid,))
        result = cursor.fetchone()
        return dict(result) if result else {}
    except sqlite3.IntegrityError as e:
        if "UNIQUE constraint failed" in str(e):
            raise ValueError("评委姓名已存在，请使用不同的姓名")
        else:
            raise ValueError(f"数据库约束错误: {e}")
    except Exception as e:
        print(f"创建评委失败: {e}")
        raise ValueError(f"创建评委失败: {e}")

def get_judges(db: sqlite3.Connection, skip: int = 0, limit: int = 100) -> List[Dict[str, Any]]:
    """获取所有评委"""
    try:
        cursor = db.cursor()
        cursor.execute(
            "SELECT * FROM judges ORDER BY created_at DESC LIMIT ? OFFSET ?",
            (limit, skip)
        )
        results = cursor.fetchall()
        return [dict(row) for row in results]
    except Exception as e:
        print(f"获取评委列表失败: {e}")
        return []

def create_contestant(db: sqlite3.Connection, contestant: ContestantCreate) -> Dict[str, Any]:
    """创建选手"""
    try:
        cursor = db.cursor()
        cursor.execute(
            "INSERT INTO contestants (number, name) VALUES (?, ?)",
            (contestant.number, contestant.name)
        )
        db.commit()
        
        # 获取刚插入的记录
        cursor.execute("SELECT * FROM contestants WHERE id = ?", (cursor.lastrowid,))
        result = cursor.fetchone()
        return dict(result) if result else {}
    except sqlite3.IntegrityError as e:
        if "UNIQUE constraint failed" in str(e):
            raise ValueError("选手编号已存在，请使用不同的编号")
        else:
            raise ValueError(f"数据库约束错误: {e}")
    except Exception as e:
        print(f"创建选手失败: {e}")
        raise ValueError(f"创建选手失败: {e}")

def get_contestants(db: sqlite3.Connection, skip: int = 0, limit: int = 100) -> List[Dict[str, Any]]:
    """获取所有选手"""
    try:
        cursor = db.cursor()
        cursor.execute(
            "SELECT * FROM contestants ORDER BY created_at DESC LIMIT ? OFFSET ?",
            (limit, skip)
        )
        results = cursor.fetchall()
        return [dict(row) for row in results]
    except Exception as e:
        print(f"获取选手列表失败: {e}")
        return []

def get_active_contestant(db: sqlite3.Connection) -> Optional[Dict[str, Any]]:
    """获取当前激活的选手"""
    cursor = db.cursor()
    cursor.execute("SELECT * FROM contestants WHERE is_active = 1")
    result = cursor.fetchone()
    return dict(result) if result else None

def set_active_contestant(db: sqlite3.Connection, contestant_id: int) -> bool:
    """设置激活的选手"""
    cursor = db.cursor()
    
    # 先取消所有选手的激活状态
    cursor.execute("UPDATE contestants SET is_active = 0")
    
    # 激活指定选手
    cursor.execute(
        "UPDATE contestants SET is_active = 1 WHERE id = ?",
        (contestant_id,)
    )
    db.commit()
    
    return cursor.rowcount > 0

def create_score(db: sqlite3.Connection, score: ScoreCreate) -> Dict[str, Any]:
    """创建评分（不允许重复评分）"""
    cursor = db.cursor()
    
    # 检查是否已存在该评委对该选手的评分
    cursor.execute(
        "SELECT * FROM scores WHERE judge_id = ? AND contestant_id = ?",
        (score.judge_id, score.contestant_id)
    )
    existing = cursor.fetchone()
    
    if existing:
        # 如果已经存在评分，抛出异常
        raise ValueError("该评委已经为这位选手打过分了，不能重复评分")
    
    # 创建新评分
    cursor.execute(
        "INSERT INTO scores (judge_id, contestant_id, score) VALUES (?, ?, ?)",
        (score.judge_id, score.contestant_id, score.score)
    )
    db.commit()
    
    # 获取刚插入的记录
    cursor.execute("SELECT * FROM scores WHERE id = ?", (cursor.lastrowid,))
    result = cursor.fetchone()
    
    if result:
        # 确保datetime字段是字符串格式
        result_dict = dict(result)
        if 'created_at' in result_dict and result_dict['created_at'] is not None:
            result_dict['created_at'] = str(result_dict['created_at'])
        return result_dict
    return {}

def get_scores_by_contestant(db: sqlite3.Connection, contestant_id: int) -> List[ScoreResponse]:
    """获取选手的评分"""
    cursor = db.cursor()
    cursor.execute('''
        SELECT s.id, j.name as judge_name, c.name as contestant_name, s.score, s.created_at
        FROM scores s
        JOIN judges j ON s.judge_id = j.id
        JOIN contestants c ON s.contestant_id = c.id
        WHERE s.contestant_id = ?
        ORDER BY s.created_at DESC
    ''', (contestant_id,))
    
    results = cursor.fetchall()
    return [
        ScoreResponse(
            id=row['id'],
            judge_name=row['judge_name'],
            contestant_name=row['contestant_name'],
            score=row['score'],
            created_at=str(row['created_at']) if row['created_at'] is not None else None
        )
        for row in results
    ]

def get_judge_by_name(db: sqlite3.Connection, judge_name: str) -> Optional[Dict[str, Any]]:
    """通过评委姓名获取评委信息"""
    cursor = db.cursor()
    cursor.execute("SELECT * FROM judges WHERE name = ?", (judge_name,))
    result = cursor.fetchone()
    return dict(result) if result else None

def check_judge_score(db: sqlite3.Connection, judge_id: int, contestant_id: int) -> bool:
    """检查评委是否已经为选手打过分"""
    cursor = db.cursor()
    cursor.execute(
        "SELECT COUNT(*) FROM scores WHERE judge_id = ? AND contestant_id = ?",
        (judge_id, contestant_id)
    )
    result = cursor.fetchone()
    return result[0] > 0

def clear_judge_scores(db: sqlite3.Connection, judge_id: int, contestant_id: int) -> bool:
    """清空评委对某选手的评分"""
    cursor = db.cursor()
    cursor.execute(
        "DELETE FROM scores WHERE judge_id = ? AND contestant_id = ?",
        (judge_id, contestant_id)
    )
    db.commit()
    return cursor.rowcount > 0

def get_all_scores_with_ranking(db: sqlite3.Connection) -> List[ContestantResponse]:
    """获取所有选手的评分和排名（按完成评分时间倒序排列）"""
    cursor = db.cursor()
    
    # 首先获取总评委数量
    cursor.execute("SELECT COUNT(*) as total_judges FROM judges")
    total_judges_result = cursor.fetchone()
    total_judges = total_judges_result['total_judges'] if total_judges_result else 0
    
    # 如果没有任何评委，返回所有选手但不显示评分
    if total_judges == 0:
        cursor.execute('''
            SELECT 
                c.id,
                c.number,
                c.name,
                NULL as average_score,
                0 as total_scores
            FROM contestants c
            ORDER BY c.created_at DESC
        ''')
        results = cursor.fetchall()
        return [
            ContestantResponse(
                id=row['id'],
                number=row['number'],
                name=row['name'],
                average_score=None,
                rank=None,
                total_scores=0
            )
            for row in results
        ]
    
    # 获取所有选手的评分情况和最后评分时间
    cursor.execute('''
        SELECT 
            c.id,
            c.number,
            c.name,
            AVG(s.score) as average_score,
            COUNT(s.id) as total_scores,
            MAX(s.created_at) as last_score_time
        FROM contestants c
        LEFT JOIN scores s ON c.id = s.contestant_id
        GROUP BY c.id, c.number, c.name
    ''')
    
    results = cursor.fetchall()
    ranked_contestants = []
    
    # 分离已完成评分和未完成评分的选手
    completed_contestants = []
    incomplete_contestants = []
    
    for row in results:
        total_scores = row['total_scores']
        contestant_data = {
            'id': row['id'],
            'number': row['number'],
            'name': row['name'],
            'average_score': row['average_score'],
            'total_scores': total_scores,
            'last_score_time': row['last_score_time']
        }
        
        # 只有所有评委都打分了才参与排名
        if total_scores == total_judges and total_judges > 0:
            completed_contestants.append(contestant_data)
        else:
            incomplete_contestants.append(contestant_data)
    
    # 对已完成评分的选手按最后评分时间倒序排序（最后评分的排在前面）
    completed_contestants.sort(key=lambda x: x['last_score_time'] if x['last_score_time'] is not None else '1970-01-01 00:00:00', reverse=True)
    
    # 为已完成评分的选手分配排名（按时间顺序）
    for i, contestant in enumerate(completed_contestants):
        ranked_contestants.append(
            ContestantResponse(
                id=contestant['id'],
                number=contestant['number'],
                name=contestant['name'],
                average_score=contestant['average_score'],
                rank=len(completed_contestants) - i,  # 按时间顺序分配排名
                position=i + 1,  # 从1开始的排名
                total_scores=contestant['total_scores']
            )
        )
    
    # 添加未完成评分的选手（不参与排名）
    for contestant in incomplete_contestants:
        ranked_contestants.append(
            ContestantResponse(
                id=contestant['id'],
                number=contestant['number'],
                name=contestant['name'],
                average_score=None,  # 未完成评分时不显示分数
                rank=None,
                total_scores=contestant['total_scores']
            )
        )
    
    return ranked_contestants

def delete_judge(db: sqlite3.Connection, judge_id: int) -> bool:
    """删除评委"""
    try:
        cursor = db.cursor()
        
        # 首先删除该评委的所有评分记录
        cursor.execute("DELETE FROM scores WHERE judge_id = ?", (judge_id,))
        
        # 然后删除评委
        cursor.execute("DELETE FROM judges WHERE id = ?", (judge_id,))
        db.commit()
        
        return cursor.rowcount > 0
    except Exception as e:
        print(f"删除评委失败: {e}")
        return False

def delete_contestant(db: sqlite3.Connection, contestant_id: int) -> bool:
    """删除选手"""
    try:
        cursor = db.cursor()
        
        # 首先删除该选手的所有评分记录
        cursor.execute("DELETE FROM scores WHERE contestant_id = ?", (contestant_id,))
        
        # 然后删除选手
        cursor.execute("DELETE FROM contestants WHERE id = ?", (contestant_id,))
        db.commit()
        
        return cursor.rowcount > 0
    except Exception as e:
        print(f"删除选手失败: {e}")
        return False 

def get_adjusted_average_for_contestant(db: sqlite3.Connection, contestant_id: int) -> Dict[str, Any]:
    """计算选手的去掉一个最高分和一个最低分后的平均分。
    规则：
    - 分数列表总数小于3时，去掉最高和最低后没有剩余分数，视为0分。
    - 否则排序后各去掉一个最高分和一个最低分，再取剩余分数的平均。
    """
    cursor = db.cursor()
    cursor.execute("SELECT score FROM scores WHERE contestant_id = ? ORDER BY score ASC", (contestant_id,))
    rows = cursor.fetchall()
    scores = [row[0] for row in rows]

    total_scores = len(scores)
    if total_scores < 3:
        adjusted_average = 0.0
    else:
        # 去掉一个最低分和一个最高分
        trimmed = scores[1:-1]
        if len(trimmed) == 0:
            adjusted_average = 0.0
        else:
            adjusted_average = sum(trimmed) / len(trimmed)

    # 基本信息
    cursor.execute("SELECT id, number, name FROM contestants WHERE id = ?", (contestant_id,))
    contestant = cursor.fetchone()
    contestant_info = dict(contestant) if contestant else None

    return {
        "contestant": contestant_info,
        "adjusted_average": adjusted_average,
        "total_scores": total_scores,
    }


def get_active_contestant_with_adjusted_average(db: sqlite3.Connection) -> Optional[Dict[str, Any]]:
    """获取当前激活选手及其去掉一个最高分和一个最低分后的平均分。"""
    active = get_active_contestant(db)
    if not active:
        return None
    result = get_adjusted_average_for_contestant(db, active['id'])
    # 补充选手 number/name/id
    result['contestant'] = {
        'id': active['id'],
        'number': active['number'],
        'name': active['name'],
    }
    return result 